﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

namespace Common.Helper
{
    public class UtilitySqlServer
    {
        /// <summary> 
        /// 将class实例对象转换为SqlParameter列表
        /// </summary>
        /// <param name="model">类实例对象,实例的成员变量不可为null</param>
        /// <returns>SqlParameter列表</returns>

        public static List<SqlParameter> ModelToParameterList(object Model)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            PropertyInfo[] Props = Model.GetType().GetProperties();
            if (Props.Length > 0)
            {
                for (int i = 0; i < Props.Length; i++)
                {
                    SqlParameter parm = new SqlParameter();
                    parm.Direction = System.Data.ParameterDirection.InputOutput;
                    parm.ParameterName = Props[i].Name;
                    var o = Props[i].GetValue(Model, null);
                    Type t = Props[i].PropertyType;
                    if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>))
                    {
                        t = Props[i].PropertyType.GetGenericArguments()[0];
                    }
                    parm.SqlDbType = GetSqlDbType(t);
                    if (o == null)
                    {
                        parm.Value = DBNull.Value;
                    }
                    else parm.Value = o;
                    parameters.Add(parm);
                }
            }
            return parameters;
        }

        /// <summary> 
        /// 获得c#类型的对应sqlDbType类型
        /// </summary>
        /// <param name="t">c#类型</param>
        /// <returns>SqlDBType</returns>
        private static SqlDbType GetSqlDbType(Type t)
        {
            SqlDbType dbType = SqlDbType.Variant;
            switch (t.Name.ToLower())
            {
                case "int16":
                    dbType = SqlDbType.SmallInt;
                    break;
                case "int32":
                    dbType = SqlDbType.Int;
                    break;
                case "int64":
                    dbType = SqlDbType.BigInt;
                    break;
                case "single":
                    dbType = SqlDbType.Real;
                    break;
                case "decimal":
                    dbType = SqlDbType.Decimal;
                    break;
                case "byte[]":
                    dbType = SqlDbType.VarBinary;
                    break;
                case "boolean":
                    dbType = SqlDbType.Bit;
                    break;
                case "string":
                    dbType = SqlDbType.NVarChar;
                    break;
                case "char[]":
                    dbType = SqlDbType.Char;
                    break;
                case "datetime":
                    dbType = SqlDbType.DateTime;
                    break;
                case "datetime2":
                    dbType = SqlDbType.DateTime2;
                    break;
                case "datetimeoffset":
                    dbType = SqlDbType.DateTimeOffset;
                    break;
                case "timespan":
                    dbType = SqlDbType.Time;
                    break;
                case "guid":
                    dbType = SqlDbType.UniqueIdentifier;
                    break;
                case "xml":
                    dbType = SqlDbType.Xml;
                    break;
                case "object":
                    dbType = SqlDbType.Variant;
                    break;
            }
            return dbType;
        }
    }
}
